<%@ page import="java.io.PrintStream" %> 
<%@ page import="java.sql.ResultSet" %> 
<%@ page import="java.sql.SQLException" %> 
<%@ page import="java.sql.Statement" %> 
<%@ page import="unilearn.da.DABase" %> 
<html>
<%
		Statement stmt = null;
		try {
			stmt = DABase.getConn().createStatement();
		} catch (SQLException e) {
			out.println(e.getMessage());
			return;
		}
		ResultSet rs = null;
		try {
			String lastSubject = "";
			long lastAssignment = -1;

			rs = stmt.executeQuery("SELECT s.studentNumber, s.submissionID, a.assignmentID, a.assignmentName, u.subjectCode FROM submission s, assignment a, Subject u WHERE a.assignmentID = s.assignmentID AND u.subjectID = a.subjectID ORDER BY u.subjectCode, a.assignmentName, s.studentNumber ");
			while (rs.next()) {
				String number = rs.getString("studentNumber");
				String assignmentName = rs.getString("assignmentName");
				String subjectName = rs.getString("subjectCode");
				long submissionID = rs.getLong("submissionID");
				long assignmentID = rs.getLong("assignmentID");

				if (!subjectName.equals(lastSubject)) {
					lastSubject = subjectName;
					out.println("<h1>Subject: " + subjectName + "</h1>");
					lastAssignment = -1;
				}
				if (assignmentID != (lastAssignment)) {
					lastAssignment = assignmentID;
					out.println("<h2>Assignment: " + assignmentName + "</h2>");
				}
				out.println(subjectName + "|<a href=\"submission.jsp?assignmentID=" + assignmentID + "\">" + assignmentName + "</a>|<a href=\"submission.jsp?assignmentID=" + assignmentID + "&submissionID=" + submissionID + "\">" + number + "</a><br/>");
			}
		} catch (SQLException e) {
			out.println(e.getMessage());
		}
%>
</html>

